import pymongo
import xlrd
import datetime
import time
import json
import math
table = 'main_kongqi'


def getSearch(title,sheets,month):
    myclient = pymongo.MongoClient("mongodb://localhost:27017/")
    mydb = myclient[table]
    mycol = mydb[title]
    x = mycol.find_one()
    sheetList = []
    returnData = {}
    monthList = []
    typesList = []
    avgs = x['avg']
    for sheet in avgs:
        sheetList.append(sheet)
    for types in avgs[sheets]:
        key=0
        for i in avgs[sheets][types]:
            key = key+1
            v = avgs[sheets][types][i]
            value = math.ceil(v*1000000)/1000000
            returnData.setdefault(i,{})
            returnData[i][types] = value
            mon = int(i.split('-')[1])
            if mon not in monthList:
                monthList.append(int(i.split('-')[1]))
            if types not in typesList:
                typesList.append(types)
            # data.append({"id":key,'date':i,"value":value})
    if int(month) > 0:
        avgs = x['dayAVG']
        for sheet in avgs:
            sheetList.append(sheet)
        returnData = {}
        for types in avgs[sheets]:
            key=0
            for i in avgs[sheets][types]:
                target_month = i.split('-')
                if int(month) == int(target_month[1]):
                    key = key+1
                    v = avgs[sheets][types][i]
                    value = math.ceil(v*10000)/10000
                    returnData.setdefault(i,{})
                    returnData[i][types] = value
        # with open('./test_text.txt','w') as file_object:
        #     file_object.write(str(returnData))
    file_handle=open('1.txt',mode='w')
    file_handle.write(str(returnData))
    return {"sheetList":sheetList,"data":returnData,"monthList":monthList,"typesList":typesList}

def delete(title):
    myclient = pymongo.MongoClient("mongodb://localhost:27017/")
    mydb = myclient[table]
    mycol = mydb[title]
    mycol.drop()

def getDetails(title,name):
    myclient = pymongo.MongoClient("mongodb://localhost:27017/")
    mydb = myclient[table]
    mycol = mydb[title]
    x = mycol.find_one()
    try:
        WD = x['XS'][name]['WD']  #温度的值
        FX = x['XS'][name]['FX']  #风向的值
        FS = x['XS'][name]['FS']  #风速的值
        SD = x['XS'][name]['SD']  #湿度的值
        QY = x['XS'][name]['QY']  #气压的值
    except:
        return {'code':-1,'msg':'该区域暂无温度，风向，风速等相关影响因素数据'}
    # print(WD)
    PM25 = x['data'][name]['PM25']# PM2.5的值
    PM25AVG = x['avg'][name]['PM25']# PM2.5的平均值
    wd,fx,fs,sd,qy,data = {},{},{},{},{},{}
    if not bool(WD):
        return {'code':-1,'msg':'该区域暂无温度，风向，风速等相关影响因素数据'}
    for date in WD:
        #取数据
        PM25S = PM25[date]  #每月份数据列
        AVGS = PM25AVG[date]  #平均值
        WDS = WD[date]   #每月份温度数据列
        FXS = FX[date]   #每月份风向数据列
        FSS = FS[date]   #每月份风速数据列
        SDS = SD[date]   #每月份湿度数据列
        QYS = QY[date]   #每月份气压数据列
        wd[date] = calcJG(WDS,PM25S,AVGS)
        fx[date] = calcJG(FXS,PM25S,AVGS)
        fs[date] = calcJG(FSS,PM25S,AVGS)
        sd[date] = calcJG(SDS,PM25S,AVGS)
        qy[date] = calcJG(QYS,PM25S,AVGS)
    data = {"code":1,"data":{'wd':wd,'fx':fx,'fs':fs,'sd':sd,'qy':qy}}
    return data
    
def calcJG(XS,PM25,AVG):
    #每月分的关系
    sums ,sumsWd,sumPM25,wdSQ= 0,0,0,0
    data = {}
    for i in range(0,len(XS)):  #与温度的关系
        sums += XS[i]*PM25[i]
        sumsWd += XS[i]
        sumPM25 += PM25[i]
        wdSQ += math.pow(XS[i],2)
    avgWD = sumsWd/len(XS) 
    fm = (sums-51766*avgWD*AVG)
    fz = (math.sqrt(wdSQ)) - (51766*math.pow(avgWD,2))*math.sqrt(math.pow(sumPM25,2))-51766*math.pow(AVG,2)
    return fm/fz
def getList():
    myclient = pymongo.MongoClient("mongodb://localhost:27017/")
    mydb = myclient[table]
    collist = mydb. list_collection_names()
    dataList = []
    i=1
    for col in collist:
        mycol = mydb[col]
        for x in mycol.find():
            avgs = x['avg']
            addtime = x['addtime']
            # sheetList = []
            data = {}
            dateList = []
            for sheet in avgs:
                # sheetList.append(sheet) 
                sheets = {}
                for types in avgs[sheet]:
                    total = 0
                    print(avgs[sheet][types])
                    if(avgs[sheet][types]):
                        for date in avgs[sheet][types]:
                            total += avgs[sheet][types][date]
                            if date not in dateList:
                                dateList.append(date)
                        avg = total/len(avgs[sheet][types])
                    sheets[types] = avg
                data[sheet] = sheets
            dataList.append({"id":i,"title":col,"addtime":addtime,"data":data,"dateList":dateList})
            i += 1
    return {"code":10001,"dataList":dataList}

def getAvgData(title):
    myclient = pymongo.MongoClient("mongodb://localhost:27017/")
    mydb = myclient[table]
    mycol = mydb[title]
    sheetList = []
    dataList = []
    x = mycol.find_one()
    for sheet in x['avg']:
        sheetList.append(sheet)
        dataList.append(x['avg'][sheet])
    return {"sheetList":sheetList,"dataList":dataList}
        


# {"title":xxx,"addtime":xxx,data:{师范大学:{PM25:xxx,}}}
#另外一个库储存数据表的名称
def insertDBname(dbname):
    myclient = pymongo.MongoClient("mongodb://localhost:27017/")
    mydb = myclient['namelist']
    mycol = mydb["dblist"]
    data = {
        "title":dbname,
        "addtime":time.asctime( time.localtime(time.time()) )
    }
    return mycol.insert_one({"title":dbname})

def excel_import2(path,title):
    myclient = pymongo.MongoClient("mongodb://localhost:27017/")
    book = xlrd.open_workbook(path)
    #连接mongodb
    mydb = myclient[table]
    mycol = mydb[title]
    sheetList = book.sheet_names()
    totalAVG = {}
    dayAVG = {}
    data = {}
    XS = {}
    for sheet in sheetList:
        sheet1 = book.sheet_by_name(sheet)
        dateList = sheet1.col(0)

        SO2 = sheet1.col(1)
        NO = sheet1.col(2)
        NO2 = sheet1.col(3)
        NOx = sheet1.col(4)
        O3 = sheet1.col(5)
        CO = sheet1.col(6)
        PM10 = sheet1.col(7)
        PM25 = sheet1.col(8)

        FS = sheet1.col(9)  #风速
        FX = sheet1.col(10)  #风向
        WD = sheet1.col(11)  #温度
        SD = sheet1.col(12)  #湿度
        QY = sheet1.col(13)  #气压

        pm25Data,SO2Data,NOData,NO2Data,NOxData,O3Data,COData,PM10Data = {},{},{},{},{},{},{},{}

        FSDATA,FXDATA,WDDATA,SDDATA,QYDATA = {},{},{},{},{}
        dayPM25,daySO2,dayNO,dayNO2,dayNOx,dayO3,dayCO,dayPM10 = {},{},{},{},{},{},{},{}  #算每日的平均值
        for j in range(1,len(dateList)):
            if dateList[j].value == '':
                continue
            date = xlrd.xldate.xldate_as_datetime(dateList[j].value, 0)  #时间
            date = str(date).split()[0]
            day = date
            date = datetime.datetime.strptime(date, "%Y-%m-%d").strftime("%Y-%m")

            pm25Data.setdefault(date,[]) 
            dayPM25.setdefault(day,[])
            if(sheet == '舟曲中学' or sheet == '西固环保局'):
                if(PM25[j].value and  float(PM25[j].value) <= 0.5 and FX[j].value and float(FX[j].value) < 360 and WD[j].value and float(WD[j].value) > -20 and SD[j].value and float(QY[j].value) > 0):
                    pm25Data[date].append(PM25[j].value)
                    dayPM25[day].append(PM25[j].value)
                    FSDATA.setdefault(date,[])
                    FXDATA.setdefault(date,[])
                    WDDATA.setdefault(date,[])
                    SDDATA.setdefault(date,[])
                    QYDATA.setdefault(date,[])
                    FSDATA[date].append(FS[j].value)
                    FXDATA[date].append(FX[j].value)
                    WDDATA[date].append(WD[j].value)
                    SDDATA[date].append(SD[j].value)
                    QYDATA[date].append(QY[j].value)
            else:
                pm25Data[date].append(PM25[j].value)
                dayPM25[day].append(PM25[j].value)

            daySO2.setdefault(day,[])
            daySO2[day].append(SO2[j].value)
            SO2Data.setdefault(date,[])
            SO2Data[date].append(SO2[j].value)

            dayNO.setdefault(day,[])
            dayNO[day].append(NO[j].value)
            NOData.setdefault(date,[])
            NOData[date].append(NO[j].value)

            dayNO2.setdefault(day,[])
            dayNO2[day].append(NO2[j].value)
            NO2Data.setdefault(date,[])
            NO2Data[date].append(NO2[j].value)

            dayNOx.setdefault(day,[])
            dayNOx[day].append(NOx[j].value)
            NOxData.setdefault(date,[])
            NOxData[date].append(NOx[j].value)

            dayO3.setdefault(day,[])
            dayO3[day].append(O3[j].value)
            O3Data.setdefault(date,[])
            O3Data[date].append(O3[j].value)

            dayCO.setdefault(day,[])
            dayCO[day].append(CO[j].value)
            COData.setdefault(date,[])
            COData[date].append(CO[j].value)

            dayPM10.setdefault(day,[])
            dayPM10[day].append(PM10[j].value)
            PM10Data.setdefault(date,[])
            PM10Data[date].append(PM10[j].value)
        
        mydict = {"PM25":pm25Data,"SO2":SO2Data,"NO":NOData,"NO2":NO2Data,"NOx":NOxData,"O3":O3Data,"CO":COData,"PM10":PM10Data}
        # filename = './'+sheet+'.txt'
        # with open(filename, 'w') as file_object:
        #     file_object.write(str(pm25Data))
        mydict2 = {"FS":FSDATA,"FX":FXDATA,"WD":WDDATA,"SD":SDDATA,"QY":QYDATA}
        data.setdefault(sheet,{})
        data[sheet] = mydict
        XS.setdefault(sheet,[])
        XS[sheet] = mydict2
        # mycol = mydb[sheet]
        # mycol.insert_one(mydict)
        #先计算各项平均值
        totalAVG.setdefault(sheet,{})
        totalAVG[sheet]["PM25"] = calcAVG(pm25Data)
        totalAVG[sheet]["SO2"] = calcAVG(SO2Data)
        totalAVG[sheet]["NO"] = calcAVG(NOData)
        totalAVG[sheet]["NO2"] = calcAVG(NO2Data)
        totalAVG[sheet]["NOx"] = calcAVG(NOxData)
        totalAVG[sheet]["O3"] = calcAVG(O3Data)
        totalAVG[sheet]["CO"] = calcAVG(COData)
        totalAVG[sheet]["PM10"] = calcAVG(PM10Data)
        dayAVG.setdefault(sheet,{})
        dayAVG[sheet]['dayPM25'] = calcAVG(dayPM25)
        dayAVG[sheet]['daySO2'] = calcAVG(daySO2)
        dayAVG[sheet]['dayNO'] = calcAVG(dayNO)
        dayAVG[sheet]['dayNO2'] = calcAVG(dayNO2)
        dayAVG[sheet]['dayNOx'] = calcAVG(dayNOx)
        dayAVG[sheet]['dayO3'] = calcAVG(dayO3)
        dayAVG[sheet]['dayCO'] = calcAVG(dayCO)
        dayAVG[sheet]['dayPM10'] = calcAVG(dayPM10)
    # exit()
    mydicts = {"title":title,"addtime":str(datetime.datetime.now()).split('.')[0],"avg":totalAVG,"XS":XS,"dayAVG":dayAVG}
    mycol.insert_one(mydicts)
    # filename = 'test_text.txt'
    # with open(filename, 'w') as file_object:
    #     file_object.write(str(mydicts))

#计算平均值
def calcAVG(calcDatas):
    avgs = {}
    for k in calcDatas:
        sums = 0
        total = len(calcDatas[k])
        for i in calcDatas[k]:
            if(i == ''):
                total -= 1
                continue
            sums += float(i)
        
        if(total <= 0):
            continue
        avg = sums/total
        avgs[k] = avg
    return avgs
